package com.ego.dao.impl;

import com.ego.dao.CartDao;
import com.ego.utils.JDBCUtils;
import com.ego.vo.CartItemVo;
import com.ego.vo.CartListVo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 袁梦达 2019012364
 */
public class CartDaoImpl implements CartDao {

    //通过账号获取购物车Id
    @Override
    public Integer getCartByUserNumber(String userNumber){
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement statement = null;
        try {
            String sql = "select * from cart where user_number = ?";
            statement = connection.prepareStatement(sql);
            statement.setString(1, userNumber);
            ResultSet resultSet = statement.executeQuery();
            int cartId;
            if(resultSet.next()){
                cartId = resultSet.getInt("id");
            }else {
                cartId = createNewCart(userNumber);
            }
            return cartId;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(statement, connection);
        }
        return null;
    }

    //创建新的购物车
    private Integer createNewCart(String userNumber){
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement statement = null;
        Integer cartId = getNewCartId();
        try {
            String sql = "insert into cart values(?, ?, 0)";
            statement = connection.prepareStatement(sql);
            statement.setInt(1, cartId);
            statement.setString(2, userNumber);
            int rowCount = statement.executeUpdate();
            if(rowCount > 0){
                return cartId;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(statement, connection);
        }
        return null;
    }

    //获取最新购物车编号
    private Integer getNewCartId(){
        int newCartId = 1;
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement statement = null;
        try {
            String sql = "select * from cart";
            statement = connection.prepareStatement(sql);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()){
                newCartId++;
            }
            return newCartId;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(statement, connection);
        }
        return null;
    }

    //将商品添加到指定用户的购物车中
    @Override
    public boolean addProduct(String userNumber, Integer productId){
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement statement = null;
        try {
            Integer cartId = getCartByUserNumber(userNumber);
            System.out.println(cartId);
            if(isExistInCart(userNumber, productId)){
                String sql = "update cart_product set quantity = quantity + 1  where cart_id = ? and product_id = ?";
                statement = connection.prepareStatement(sql);
                statement.setInt(1, cartId);
                statement.setInt(2, productId);
                int rowCount = statement.executeUpdate();
                boolean bool = changeCartPrice(userNumber, productId);
                if(rowCount > 0 && bool){
                    return true;
                }
                return false;
            }else {
                String sql = "INSERT INTO cart_product VALUES(?, ?, ?, ?)";
                statement = connection.prepareStatement(sql);
                statement.setInt(1, cartId);
                statement.setInt(2, productId);
                statement.setInt(3, 1);
                statement.setInt(4, 1);
                int rowCount = statement.executeUpdate();
                boolean bool = changeCartPrice(userNumber, productId);
                if(rowCount > 0 && bool){
                    return true;
                }
                return false;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(statement, connection);
        }
        return false;
    }

    //添加物品后更新购物车的总价
    private boolean changeCartPrice(String userNumber, Integer productId){
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement statement = null;
        try {
            String sql = "update cart set sum_price = sum_price + ?";
            statement = connection.prepareStatement(sql);
            statement.setDouble(1, getPrice(productId));
            int rowCount = statement.executeUpdate();
            if(rowCount > 0){
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(statement, connection);
        }
        return false;
    }

    //查询当前物品的单价
    private Double getPrice(Integer productId){
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement statement = null;
        try {
            String sql = "SELECT price FROM product WHERE id = ?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1, productId);
            ResultSet resultSet = statement.executeQuery();
            if(resultSet.next()){
                return resultSet.getDouble("price");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(statement, connection);
        }
        return null;
    }

    //查询购物车中是否已经有该商品
    private boolean isExistInCart(String userNumber, Integer productId){
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement statement = null;
        try {
            Integer cartId = getCartByUserNumber(userNumber);
            String sql = "select * from  cart_product where cart_id = ? and product_id = ?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1, cartId);
            statement.setInt(2, productId);
            ResultSet resultSet = statement.executeQuery();
            if(resultSet.next()){
                return true;
            }
            return false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(statement, connection);
        }
        return false;
    }

    //展示购物车
    @Override
    public CartListVo showCart(String userNumber){
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement statement = null;
        try {
            String sql = "SELECT * FROM cart, cart_product, product WHERE cart.id = cart_product.cart_id AND cart_product.product_id = product.id AND cart.user_number = ?";
            statement = connection.prepareStatement(sql);
            statement.setString(1, userNumber);
            CartListVo cartListVo = new CartListVo();
            cartListVo.setCartId(getCartByUserNumber(userNumber));
            cartListVo.setUserNumber(userNumber);
            cartListVo.setSumPrice(0);

            List<CartItemVo> cartItemVoList = new ArrayList<>();
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()){
                cartListVo.setSumPrice(resultSet.getDouble("sum_price"));
                CartItemVo cartItemVo = new CartItemVo();
                cartItemVo.setProductId(resultSet.getInt("product_id"));
                cartItemVo.setBaseInformation(resultSet.getString("base_information"));
                cartItemVo.setImage(resultSet.getString("image"));
                cartItemVo.setPrice(resultSet.getDouble("price"));
                cartItemVo.setQuantity(resultSet.getInt("quantity"));
                cartItemVo.setSumPrice(cartItemVo.getPrice() * cartItemVo.getQuantity());
                cartItemVoList.add(cartItemVo);
            }
            cartListVo.setCartItemVoList(cartItemVoList);
            return cartListVo;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(statement, connection);
        }
        return null;
    }

    //将商品从指定用户的购物车中删除
    @Override
    public boolean removeProduct(String userNumber, Integer productId){
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement statement = null;
        try {
            Integer cartId = getCartByUserNumber(userNumber);
            if(isExistInCart(userNumber, productId)){
                boolean b = changeCartPriceWhenRemove(userNumber, productId);
                String sql = "delete from cart_product where cart_id = ? and product_id = ?";
                statement = connection.prepareStatement(sql);
                statement.setInt(1, cartId);
                statement.setInt(2, productId);
                int rowCount = statement.executeUpdate();
                if(rowCount > 0 && b){
                    return true;
                }
                return false;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(statement, connection);
        }
        return false;
    }
    //移除物品后更新购物车的总价
    private boolean changeCartPriceWhenRemove(String userNumber, Integer productId){
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement statement = null;
        try {
            String sql = "update cart set sum_price = sum_price - ?";
            statement = connection.prepareStatement(sql);
            System.out.println("个数："+ getQuantityByProductId(userNumber, productId));
            statement.setDouble(1, getPrice(productId) * getQuantityByProductId(userNumber, productId));
            int rowCount = statement.executeUpdate();
            if(rowCount > 0){
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(statement, connection);
        }
        return false;
    }

    //查询购物车中当前物品的数量
    private Integer getQuantityByProductId(String userNumber, Integer productId){
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement statement = null;
        Integer cartId = getCartByUserNumber(userNumber);
        try {
            String sql = "SELECT quantity FROM cart_product WHERE cart_product.cart_id = ? AND cart_product.product_id = ?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1, cartId);
            statement.setInt(2, productId);
            ResultSet resultSet = statement.executeQuery();
            if(resultSet.next()){
                return resultSet.getInt("quantity");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(statement, connection);
        }
        return null;
    }
}
